#导入pymysql模块
import pymysql 

#stu表操作类
class Studb:
	def __init__(self):
		#获取数据库连接
		self.db = pymysql.connect(host='localhost',user='root',password='huangbin1990',charset='utf8')
		#创建游标对象
		self.cursor = self.db.cursor()
		#寻找是否有stu数据库
		try:
			self.cursor.execute("show databases;")
			for i in self.cursor.fetchall():
				if i ==('stu',):
					if input('已有数据库stu，是否删除并重新创建？（y/n）')=='y':
						self.cursor.execute("drop database stu ;")

			self.cursor.execute("create database stu;")
			self.cursor.execute("use stu")
			self.cursor.execute('create table student(id int unsigned unique NOT NULL PRIMARY KEY AUTO_INCREMENT,name varchar(20) NOT NULL,age int unsigned NOT NULL,classid VARCHAR(20) NOT NULL);')
			self.cursor.execute("insert into student VALUES (NULL,'zhangsan',20,'python02'),(NULL,'lisi',22,'python03'),(NULL,'wangwu',25,'python04');")
 			#事务提交
			self.db.commit()

		except Exception as err:
			self.db.rollback() 
			print("连接错误：",err)



	def findAll(self):
		#查询
		try:
			self.cursor.execute("select * from student;")
			if self.cursor.rowcount== 0:
				print ("===========没有学员信息！=============")
			else: 
				print("|{0:<5}| {1:<10}| {2:<5}| {3:<10}|".format("id", "name", "age", "classid"))
				print("-" * 40)
				for i in self.cursor.fetchall():
					print("|{0:<5}| {1:<10}| {2:<5}| {3:<10}|".format(i[0], i[1], i[2],i[3]))

		except Exception as err:
			self.rollback() 
			print("新增失败！,原因是：",err)
	def insert(self,stu):
		#新增
		try:
			self.cursor.execute('insert into student values(null,"{}","{}","{}");'.format(stu["name"],stu["age"],stu["classid"]))
		except Exception as err:
			self.db.rollback()
			print('新增失败！,原因是：', err)

	def delete(self,id):		
		#删除
		try:
			self.cursor.execute('delete from student where id={};'.format(id))
		except Exception as err:
			self.db.rollback()
			print('删除失败！,原因是：', err)

	#析构方法关闭
	def __del__(self):
		self.cursor.close()
		self.db.close()
		

#主程序
Studb = Studb()
while True:
	# 输出初始界面
	print("="*12,"学员管理系统","="*14)
	print("{0:1} {1:13} {2:15}".format(" ","1. 查看学员信息","2. 添加学员信息"))
	print("{0:1} {1:13} {2:15}".format(" ","3. 删除学员信息","4. 退出系统"))
	print("="*40)
	key = input("请输入对应的选择：")
	# 根据键盘值，判断并执行对应的操作
	if key == "1":
		print("="*12,"学员信息浏览","="*14)
		Studb.findAll()
		input("按回车键继续：")
	elif key == "2":
		print("="*12,"学员信息添加","="*14)
		stu={}
		stu['name']=input("请输入要添加的姓名：")
		stu['age']=input("请输入要添加的年龄：")
		stu['classid']=input("请输入要添加的班级号：")
		Studb.insert(stu)
		Studb.findAll()
		input("按回车键继续：")
	elif key == "3":
		print("="*12,"学员信息删除","="*14)
		Studb.findAll()
		id = input("输入要删除的ID号：")
		Studb.delete(id)
		Studb.findAll()
		input("按回车键继续：")
	elif key == "4":
		print("="*12,"再见","="*14)
		break
	else:
		print("======== 无效的键盘输入！ ==========")